﻿using FS.Common.DotNetCode;
using FS.Common.DotNetData;
using FS.Common.DotNetBean;
using FS.Common.DotNetEncrypt;
using FS.DataBase;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Linq;

namespace FS.Business
{
    public class System_Dao
    {
        #region 用户登陆日志
        public void SysLoginLog(string SYS_USER_ACCOUNT, string SYS_LOGINLOG_STATUS, string OWNER_address)
        {
            Hashtable ht = new Hashtable();
            ht["SYS_LOGINLOG_ID"] = CommonHelper.GetGuid;
            ht["User_Account"] = SYS_USER_ACCOUNT;
            ht["SYS_LOGINLOG_IP"] = RequestHelper.GetIP();
            ht["OWNER_address"] = OWNER_address;
            ht["SYS_LOGINLOG_STATUS"] = SYS_LOGINLOG_STATUS;
            DataFactory.Run().InsertByHashtable("Sys_Loginlog", ht);
        }
        #endregion

        #region 首页数据信息
        public DataTable GetHomeShortcut_List(string User_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM Sys_QuickLink WHERE User_ID = @User_ID");
            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@User_ID", User_ID)
			};
            return DataFactory.Run().GetDataTableBySQL(strSql, para);
        }

        /// <summary>
        /// 本月登陆日志
        /// </summary>
        /// <param name="count"></param>
        /// <returns></returns>
        public DataTable GetLogin_Info(ref int count)
        {
            DateTime now = DateTime.Now;
            DateTime d = new DateTime(now.Year, now.Month, 1);
            DateTime d2 = d.AddMonths(1).AddDays(-1.0);
            string UserAccount = RequestSession.GetSessionUser().UserAccount.ToString();
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSqlCount = new StringBuilder();
            strSql.Append("Select top 2 SYS_LOGINLOG_IP,Sys_LoginLog_Time from Sys_Loginlog where User_Account = @User_Account");
            strSql.Append(" and Sys_LoginLog_Time >= @BeginBuilTime");
            strSql.Append(" and Sys_LoginLog_Time <= @endBuilTime ORDER BY Sys_LoginLog_Time DESC ");
            strSqlCount.Append("Select count(1) from Sys_Loginlog where User_Account = @User_Account");
            strSqlCount.Append(" and Sys_LoginLog_Time >= @BeginBuilTime");
            strSqlCount.Append(" and Sys_LoginLog_Time <= @endBuilTime");
            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@User_Account", UserAccount),
				new SqlParam("@BeginBuilTime", d),
				new SqlParam("@endBuilTime", d2)
			};
            count = Convert.ToInt32(DataFactory.Run().GetObjectValue(strSqlCount, para));
            return DataFactory.Run().GetDataTableBySQL(strSql, para);
        }
        #endregion

        /// -------------------
       

        #region 用户权限控制
        public DataTable InitUserRole(string Roles_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT R.User_ID,U.User_Account+'|'+U.User_Name AS User_Name");
            strSql.Append(" FROM Sys_UserRole R ");
            strSql.Append("   inner JOIN Sys_UserInfo U ON U.User_ID=R.User_ID ");
            strSql.Append("WHERE Roles_ID = @Roles_ID");

            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@Roles_ID", Roles_ID)
			};
            return DataFactory.Run().GetDataTableBySQL(strSql, para);
        }

        public DataTable InitUserRoleByUser(string User_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Roles_ID FROM sys_UserRole WHERE User_ID = @User_ID");
            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@User_ID", User_ID)
			};
            return DataFactory.Run().GetDataTableBySQL(strSql, para);

        }

        public DataTable InitUserRight(string User_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Menu_Id FROM Sys_UserRight WHERE User_ID = @User_ID");
            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@User_ID", User_ID)
			};
            return DataFactory.Run().GetDataTableBySQL(strSql, para);
        }       

        #endregion

        #region 用户菜单权限
        //用户菜单
        public DataTable GetMenuHtml(string UserId)
        {            
            Hashtable ht = new Hashtable();
            ht.Add("user_id", UserId);
            return DataFactory.Run().GetDataTableProc("usp_sys_GetMenuByUser", ht);           
        }

        //用户菜单地址
        public DataTable GetPermission_URL(string UserId)
        {
            DataTable dt = new DataTable();
            
            if (CacheHelper.GetCache("KeyPermission_URL") == null)
            {
                dt = DataFactory.Run().GetDataTableProc("usp_sys_GetMenuURL", null); 
                CacheHelper.Insert("KeyPermission_URL", dt);
            }
            else
            {
                dt = (DataTable)CacheHelper.GetCache("KeyPermission_URL");
            }
            return DataTableHelper.GetNewDataTable(dt, "user_id= '" + UserId + "'");
        }
        #endregion
              
        
        public string GetMenuByNavigateUrl(string NavigateUrl, DataTable dt_Menu)
        {
            string result;
            try
            {
                DataTable dt = DataTableHelper.GetNewDataTable(dt_Menu, "Menu_Url='" + NavigateUrl + "'");
                result = dt.Rows[0]["Menu_Id"].ToString();
            }
            catch
            {
                result = "";
            }
            return result;
        }

        #region 菜单
        public DataTable GetMenuList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Menu_Id,Menu_Name,Menu_Img,Menu_Type,TARGET,ParentId,");
            strSql.Append(" CAST(Menu_Type AS VARCHAR(10)) +'-'+CAST(SortCode AS VARCHAR(10)) AS Sort,");
            strSql.Append("Menu_Url,CreateUserName,CreateDate,ModifyUserName,ModifyDate");
            strSql.Append(" FROM Sys_Menu WHERE DeleteMark = 1 and Menu_Type !=3 ORDER BY SortCode ASC");
            return DataFactory.Run().GetDataTableBySQL(strSql);
        }

        public DataTable GetMenuBind()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM Sys_Menu WHERE DeleteMark = 1 ORDER BY SortCode ASC");
            return DataFactory.Run().GetDataTableBySQL(strSql);
        }

        #endregion

        /// <summary>
        /// 登陆校验
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public DataTable UserLogin(string name, string pwd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select User_ID,User_Account,User_Pwd,User_Name,DeleteMark,isViewAll ");
            strSql.Append(",isViewSupply,isViewPrice,isViewCost ");
            strSql.Append(" from Sys_UserInfo where ");
            strSql.Append(" User_Account=@User_Account ");
            strSql.Append(" and User_Pwd=@User_Pwd ");
            strSql.Append(" and DeleteMark!=0");
           
            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@User_Account", name),
				new SqlParam("@User_Pwd", Md5Helper.MD5(pwd, 32))
			};
            return DataFactory.Run().GetDataTableBySQL(strSql, para);
        }        

        public DataTable GetSysLoginLogPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * from Sys_Loginlog where 1=1");
            strSql.Append(SqlWhere);
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "SYS_LOGINLOG_TIME", "Desc", pageIndex, pageSize, ref count);
        }

        #region 按钮事件
        public DataTable GetButtonHtml(string UserId)
        {         
            string URL = RequestHelper.GetScriptName;

            Hashtable ht = new Hashtable();
            ht.Add("User_ID", UserId);
            DataTable dt_Menu = DataFactory.Run().GetDataTableProc("usp_sys_GetButtonByUser", ht);
            
            return DataTableHelper.GetNewDataTable(dt_Menu, "ParentId='" + this.GetMenuByNavigateUrl(URL, dt_Menu) + "' AND Menu_Type = 3");
        }

        public DataTable GetButtonList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM Sys_Button WHERE DELETEMARK = 1 ORDER BY SORTCODE ASC");
            return DataFactory.Run().GetDataTableBySQL(strSql);
        }

        public DataTable GetSysMenuByButton(string Menu_Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM Sys_Menu ");
            strSql.Append("WHERE ParentId =@Menu_Id ");
            strSql.Append("AND DELETEMARK = 1 AND Menu_Type = 3 ORDER BY SORTCODE ASC");
            SqlParam[] parm = new SqlParam[]
			{
				new SqlParam("@Menu_Id", Menu_Id)
			};
            return DataFactory.Run().GetDataTableBySQL(strSql, parm);
        }

        public int AllotButton(string pkVal, string ParentId)
        {
            int result;
            try
            {
                DataTable dt_Button = this.GetButtonList();
                DataTable Newdt_Button = DataTableHelper.GetNewDataTable(dt_Button, "Button_ID = '" + pkVal + "'");
                string Button_Name = Newdt_Button.Rows[0]["Button_Name"].ToString();
                string Button_Img = Newdt_Button.Rows[0]["Button_Img"].ToString();
                string Button_Code = Newdt_Button.Rows[0]["Button_Code"].ToString();
                Hashtable ht = new Hashtable();
                ht["Menu_Id"] = CommonHelper.GetGuid;
                ht["ParentId"] = ParentId;
                ht["Menu_Name"] = Button_Name;                
                ht["Menu_Img"] = Button_Img;
                ht["Menu_Type"] = 3;
                ht["Menu_Url"] = Button_Code;
                ht["SortCode"] = CommonHelper.GetInt(DataFactory.Run().GetObjectValue(new StringBuilder("SELECT MAX(SortCode) FROM Sys_Menu WHERE ParentId = '" + ParentId + "' AND DELETEMARK = 1 AND Menu_Type = 3"))) + 1;
                ht["Target"] = "Onclick";
                ht["CreateUserId"] = RequestSession.GetSessionUser().UserId;
                ht["CreateUserName"] = RequestSession.GetSessionUser().UserName;
                result = DataFactory.Run().InsertByHashtable("Sys_Menu", ht);
            }
            catch (Exception)
            {
                result = -1;
            }
            return result;
        }

        #endregion
        
        #region 系统数据删除
        public int DeleteData_Base(string tableName, string pkName, string[] pkVal)
        {
            return DataFactory.Run().BatchDeleteData(tableName, pkName, pkVal);
        }
        public int Virtualdelete(string module, string tableName, string pkName, string[] pkVal)
        {
            int num = 0;
            int result;
            try
            {
                StringBuilder[] sqls = new StringBuilder[pkVal.Length ];
                object[] objs = new object[pkVal.Length ];
                int index = 0;
                for (int i = 0; i < pkVal.Length; i++)
                {
                    string item = pkVal[i];
                    StringBuilder sbEdit = new StringBuilder();
                    sbEdit.Append(" Update ");
                    sbEdit.Append(tableName);
                    sbEdit.Append(" Set DeleteMark = 0");
                    sbEdit.Append(" Where ").Append(pkName).Append("=").Append("@ID");
                    SqlParam[] parmEdit = new SqlParam[]
					{
						new SqlParam("@ID", item)
					};
                    sqls[index] = sbEdit;
                    objs[index] = parmEdit;
                    index++;                    
                }
                num = DataFactory.Run().BatchExecuteBySql(sqls, objs);
            }
            catch
            {
                result = num;
                return result;
            }
            result = num;
            return result;
        }
        #endregion

        #region 用户角色管理
        public DataTable InitRoleList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Roles_ID, ParentId, Roles_Name, Roles_Remark, SortCode, DeleteMark,");
            strSql.Append(" CreateDate,CreateUserName, ModifyDate, ModifyUserName");
            strSql.Append(" FROM Sys_Roles WHERE DeleteMark != 0 ORDER BY SortCode ASC");
            return DataFactory.Run().GetDataTableBySQL(strSql);
        }

        public DataTable InitRoleParentId()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Roles_ID,Roles_Name+' - '+CASE ParentId WHEN '0' THEN '父节' ELSE  '子节' END AS Roles_Name ");
            strSql.Append(" FROM Sys_Roles WHERE DeleteMark = 1 ORDER BY SortCode ASC");
            return DataFactory.Run().GetDataTableBySQL(strSql);
        }        

        public bool Add_RoleAllotMember(string[] pkVal, string Roles_ID)
        {
            bool result;
            try
            {
                StringBuilder[] sqls = new StringBuilder[pkVal.Length + 1];
                object[] objs = new object[pkVal.Length + 1];
                StringBuilder sbDelete = new StringBuilder();
                sbDelete.Append("Delete From Sys_UserRole Where Roles_ID =@Roles_ID");
                SqlParam[] parm = new SqlParam[]
				{
					new SqlParam("@Roles_ID", Roles_ID)
				};
                sqls[0] = sbDelete;
                objs[0] = parm;
                int index = 1;
                for (int i = 0; i < pkVal.Length; i++)
                {
                    string item = pkVal[i];
                    if (item.Length > 0)
                    {
                        StringBuilder sbadd = new StringBuilder();
                        sbadd.Append("Insert into Sys_UserRole(");
                        sbadd.Append("UserRole_ID,User_ID,Roles_ID,CreateUserId,CreateUserName");
                        sbadd.Append(")Values(");
                        sbadd.Append("@UserRole_ID,@User_ID,@Roles_ID,@CreateUserId,@CreateUserName)");
                        SqlParam[] parmAdd = new SqlParam[]
						{
							new SqlParam("@UserRole_ID", CommonHelper.GetGuid),
							new SqlParam("@User_ID", item),
							new SqlParam("@Roles_ID", Roles_ID),
							new SqlParam("@CreateUserId", RequestSession.GetSessionUser().UserId),
							new SqlParam("@CreateUserName", RequestSession.GetSessionUser().UserName)
						};
                        sqls[index] = sbadd;
                        objs[index] = parmAdd;
                        index++;
                    }
                }
                result = (DataFactory.Run().BatchExecuteBySql(sqls, objs) >= 0);
            }
            catch
            {
                result = false;
            }
            return result;
        }

        public DataTable Load_UserList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT 'group1' as Group_ID,'未分配角色' as Group_Name,'0' as ParentId,'0' AS isUser ");
            strSql.Append("UNION ALL ");
            strSql.Append("SELECT 'group2' as Group_ID,'已分配角色' as Group_Name,'0' as ParentId,'0' AS isUser ");
            strSql.Append("UNION ALL ");
            strSql.Append("select user_id,U.User_account+'|'+U.User_Name AS User_Name, ");
            strSql.Append("case when (select count(1) from Sys_UserRole r where r.user_id=u.user_id)=0 then 'group1' else 'group2' end as ParentId, ");
	        strSql.Append(" '1' AS isUser ");
	        strSql.Append("from sys_UserInfo U ");
            strSql.Append("where U.DeleteMark=1 ");
            return DataFactory.Run().GetDataTableBySQL(strSql);
        }
        public DataTable InitRoleRight(string Roles_ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT Menu_Id FROM Sys_RoleRight WHERE Roles_ID = @Roles_ID");
            SqlParam[] para = new SqlParam[]
			{
				new SqlParam("@Roles_ID", Roles_ID)
			};
            return DataFactory.Run().GetDataTableBySQL(strSql, para);
        }

        public bool Add_RoleAllotAuthority(string[] pkVal, string Roles_ID)
        {
            bool result;
            try
            {
                StringBuilder[] sqls = new StringBuilder[pkVal.Length + 1];
                object[] objs = new object[pkVal.Length + 1];
                StringBuilder sbDelete = new StringBuilder();
                sbDelete.Append("Delete From Sys_RoleRight Where Roles_ID =@Roles_ID");
                SqlParam[] parm = new SqlParam[]
				{
					new SqlParam("@Roles_ID", Roles_ID)
				};
                sqls[0] = sbDelete;
                objs[0] = parm;
                int index = 1;
                for (int i = 0; i < pkVal.Length; i++)
                {
                    string item = pkVal[i];
                    if (item.Length > 0)
                    {
                        StringBuilder sbadd = new StringBuilder();
                        sbadd.Append("Insert into Sys_RoleRight(");
                        sbadd.Append("RoleRight_ID,Roles_ID,Menu_Id,CreateUserId,CreateUserName");
                        sbadd.Append(")Values(");
                        sbadd.Append("@RoleRight_ID,@Roles_ID,@Menu_Id,@CreateUserId,@CreateUserName)");
                        SqlParam[] parmAdd = new SqlParam[]
						{
							new SqlParam("@RoleRight_ID", CommonHelper.GetGuid),
							new SqlParam("@Roles_ID", Roles_ID),
							new SqlParam("@Menu_Id", item),
							new SqlParam("@CreateUserId", RequestSession.GetSessionUser().UserId),
							new SqlParam("@CreateUserName", RequestSession.GetSessionUser().UserName)
						};
                        sqls[index] = sbadd;
                        objs[index] = parmAdd;
                        index++;
                    }
                }
                result = (DataFactory.Run().BatchExecuteBySql(sqls, objs) >= 0);
            }
            catch
            {
                result = false;
            }
            return result;
        }

        #endregion

        #region 用户管理
        public DataTable GetUserInfoPage(StringBuilder SqlWhere, IList<SqlParam> IList_param, int pageIndex, int pageSize, ref int count)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT U.User_ID,U.User_Name,U.User_Account,U.Email,U.DeleteMark,U.User_Remark,U.CreateDate ");
            strSql.Append(" from Sys_UserInfo U where U.DeleteMark !=0");
            strSql.Append(SqlWhere);            
            return DataFactory.Run().GetPageList(strSql.ToString(), IList_param.ToArray<SqlParam>(), "CreateDate", "Desc", pageIndex, pageSize, ref count);
        }

       
        #endregion

        public DataTable GetSelect_CurrentUserList()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select User_ID,User_name from Sys_UserInfo ");
            strSql.Append(" where DeleteMark=1 and User_ID<>'48f3889c-af8d-401f-ada2-c383031af92d'");
            return DataFactory.Run().GetDataTableBySQL(strSql);
        }
    }
}